﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using Ext.Net;
using CommonLib.BLL;

public partial class CRM_Store_ERP_SaleQuery : BasePage
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ViewState_OrderSre = "";
            StoreBind();
            gridPanel_listdata.Reload();
            DateTime time1 = new DateTime(int.Parse(extcomb_year.SelectedItem.Value), int.Parse(extcomb_month.SelectedItem.Value), 1);
            DateTime time2 = time1.AddMonths(1);
        }
    }

    private void StoreBind()
    {
        DataTable dt = SYS_USER.GetList("IS_MANAGER=1").Tables[0];
        Store_USER.DataSource = dt;
        Store_USER.DataBind();
        System.DateTime currentTime = new System.DateTime();
        currentTime = System.DateTime.Now;
        extcomb_year.SelectedItem.Value = currentTime.Year.ToString();
        extcomb_month.SelectedItem.Value = currentTime.Month.ToString();
    }

    string _QueryString;
    private string GetQryString()
    {
        string result = " 1=1 ";

        //if (exttxtf_DemandPlanNo.Text.Trim() != "")
        //    result += " and ExportBillNo like'%" + FilterSpecChar(exttxtf_DemandPlanNo.Text.Trim()) + "%'";
        //if (exttxtf_Creater.Text.Trim() != "")
        //    result += " and InUser like'%" + FilterSpecChar(exttxtf_Creater.Text.Trim()) + "%'";
        return result;
    }

    protected void MyData_Refresh(object sender, StoreRefreshDataEventArgs e)
    {
        _QueryString = GetQryString();
        int count = 0;
            count = CRMMis.BLL.ERP_PersonStock.GetList(_QueryString).Tables[0].Rows.Count;
        BindGridPSData(e, ViewState_OrderSre, PagingToolbar1, Store1, count);
    }

    protected void OnBtnLendClick(object sender, DirectEventArgs e)
    {
        DataTable dt = CRMMis.BLL.ERP_Lendgather.GetList("year='"+extcomb_year.SelectedItem.Value+"' and month='"+extcomb_month.SelectedItem.Value+"'").Tables[0];
        if (dt.Rows.Count > 0)
        {
            X.Msg.Alert("系统提示", "已有相关信息！").Show();
            return;
        }
        DateTime time1 = new DateTime(int.Parse(extcomb_year.SelectedItem.Value), int.Parse(extcomb_month.SelectedItem.Value), 1);
        DateTime time2 = time1.AddMonths(1).AddDays(-1);
        DataTable dt1 = CommonLib.BLL.BS_DATASET_SQL.SqlGetTable(@"select sql1.*,sql2.ReturnCount,sql2.ReturnMoney from(
            select a.Owner, sum(b.Count) as LendCount, sum(b.Count * e.Supplyprice) LendMoney from ERP_Lend a
            left join ERP_Lend_Detail b on a.LendId = b.LendId 
            left join ERP_StockDetail c on b.ItemId = c.ItemId
            Left join ERP_StockInfo d on c.StoreId = d.StoreId
            Left join ERP_Cargo_Detail e on d.DetailId = e.DetailId where a.CheckDate>'"+time1+ "' and a.CheckDate<='"+time2+ "' and a.State=2  group by a.Owner) sql1 inner join ( select a.Owner, sum(b.Count) as ReturnCount, sum(b.Count * e.Supplyprice) ReturnMoney  from ERP_PerReStore a LEFT JOIN ERP_PerReStoreDetail b on a.PerReturnId = b.PerReturnId left join ERP_StockDetail c on b.ItemId = c.ItemId Left join ERP_StockInfo d on c.StoreId = d.StoreId Left join ERP_Cargo_Detail e on d.DetailId = e.DetailId where a.CheckDate>'" + time1 + "' and a.CheckDate<='" + time2 + "' and a.State=2  group by a.Owner) sql2 on sql1.Owner = sql2.Owner");
        CommonLib.BLL.MyTransaction mytrans = new CommonLib.BLL.MyTransaction();
        mytrans.BeginTransaction();
        try
        {
            foreach (DataRow dr in dt1.Rows)
            {
                CRMMis.Model.ERP_Lendgather model = new CRMMis.Model.ERP_Lendgather();
                model.Id = CRMMis.BLL.ERP_Lendgather.GetNewID();
                model.Saler = dr["Owner"].ToString();
                model.year = int.Parse(extcomb_year.SelectedItem.Value);
                model.Month = int.Parse(extcomb_month.SelectedItem.Value);
                model.LendCount = string.IsNullOrEmpty(dr["LendCount"].ToString()) ? 0 : int.Parse(dr["LendCount"].ToString());
                model.LendMoney = string.IsNullOrEmpty(dr["LendMoney"].ToString()) ? 0 : decimal.Parse(dr["LendMoney"].ToString());
                model.ReturnCount = string.IsNullOrEmpty(dr["ReturnCount"].ToString()) ? 0 : int.Parse(dr["ReturnCount"].ToString());
                model.ReturnMoney = string.IsNullOrEmpty(dr["ReturnMoney"].ToString()) ? 0 : decimal.Parse(dr["ReturnMoney"].ToString());
                CRMMis.BLL.ERP_Lendgather.Add(mytrans, model);
            }
            mytrans.CommitTran();
            X.Msg.Alert("系统提示", "生成成功！").Show();
        }
        catch (Exception ex)
        {
            mytrans.RollBackTran();
            X.Msg.Alert("系统提示", ex.ToString()).Show();
        }
    }

    protected void OnBtnSaleClick(object sender, DirectEventArgs e)
    {
        DataTable dt = CRMMis.BLL.ERP_Paygather.GetList("year='" + extcomb_year.SelectedItem.Value + "' and month='" + extcomb_month.SelectedItem.Value + "'").Tables[0];
        if (dt.Rows.Count > 0)
        {
            X.Msg.Alert("系统提示", "已有相关信息！").Show();
            return;
        }
        DateTime time1 = new DateTime(int.Parse(extcomb_year.SelectedItem.Value), int.Parse(extcomb_month.SelectedItem.Value), 1);
        DateTime time2 = time1.AddMonths(1).AddDays(-1);
        DataTable dt1 = CommonLib.BLL.BS_DATASET_SQL.SqlGetTable(@"
            select a.Saler Owner, sum(b.Count) as PayCount, sum(b.Count * c.Supplyprice) PayMoney from ERP_SalePay a
            left join ERP_SalePay_Detail b on a.PayId = b.PayId 
            left join V_CargoRelation c on b.Sos_No = c.Sos_No
            where a.CheckDate>'" + time1 + "' and a.CheckDate<='" + time2 + "' and a.state=2 group by a.Saler");
        CommonLib.BLL.MyTransaction mytrans = new CommonLib.BLL.MyTransaction();
        mytrans.BeginTransaction();
        try
        {
            foreach (DataRow dr in dt1.Rows)
            {
                CRMMis.Model.ERP_Paygather model = new CRMMis.Model.ERP_Paygather();
                model.Id = CRMMis.BLL.ERP_Paygather.GetNewID();
                model.Saler = dr["Owner"].ToString();
                model.year = int.Parse(extcomb_year.SelectedItem.Value);
                model.Month = int.Parse(extcomb_month.SelectedItem.Value);
                model.PayCount = string.IsNullOrEmpty(dr["PayCount"].ToString()) ? 0 : int.Parse(dr["PayCount"].ToString());
                model.PayMoney = string.IsNullOrEmpty(dr["PayMoney"].ToString()) ? 0 : decimal.Parse(dr["PayMoney"].ToString());
                CRMMis.BLL.ERP_Paygather.Add(mytrans, model);
            }
            mytrans.CommitTran();
            X.Msg.Alert("系统提示", "生成成功！").Show();
        }
        catch (Exception ex)
        {
            mytrans.RollBackTran();
            X.Msg.Alert("系统提示", ex.ToString()).Show();
        }
    }

    protected void OnBtnSalePayClick(object sender, DirectEventArgs e)
    {
        DateTime tn = System.DateTime.Now;
        DataTable dt = CRMMis.BLL.ERP_LendSale.GetList("year='" + extcomb_year.SelectedItem.Value + "' and month='" + extcomb_month.SelectedItem.Value + "'").Tables[0];
        if (dt.Rows.Count > 0)
        {
            X.Msg.Alert("系统提示", "已有相关信息！").Show();
            return;
        }
        DateTime time1 = new DateTime(int.Parse(extcomb_year.SelectedItem.Value), int.Parse(extcomb_month.SelectedItem.Value), 1);
        DateTime time2 = time1;
        int year = time2.Year;
        int month = time2.Month;
        DateTime time3= time1.AddMonths(-1);
        int lyear = time3.Year;
        int lmonth = time3.Month;
        DataTable dt1 = CommonLib.BLL.BS_DATASET_SQL.SqlGetTable(@"
            select a.Saler,a.year,a.Month,(a.LendCount-a.ReturnCount) LendCount,
            (a.LendMoney - a.ReturnMoney) LendMoney, b.PayCount, b.PayMoney from ERP_Lendgather a
            left join ERP_Paygather b on a.Saler = b.Saler and a.year = b.year and a.month = b.Month where a.year="+year+ " and a.Month="+month+"");
        CommonLib.BLL.MyTransaction mytrans = new CommonLib.BLL.MyTransaction();
        mytrans.BeginTransaction();
        try
        {
            foreach (DataRow dr in dt1.Rows)
            {
                CRMMis.Model.ERP_LendSale model = new CRMMis.Model.ERP_LendSale();
                model.Id = CRMMis.BLL.ERP_LendSale.GetNewID();
                model.Saler = dr["Saler"].ToString();
                model.year = year;
                model.month = month;
                model.LendCount = int.Parse(dr["LendCount"].ToString());
                model.LendMoney = decimal.Parse(dr["LendMoney"].ToString());
                model.SalePayCount = int.Parse(dr["PayCount"].ToString());
                model.SalePayMoney = decimal.Parse(dr["PayMoney"].ToString());
                model.InUser = BS_LoginUserID;
                model.InDate = tn.ToString();
                DataTable dtn = CRMMis.BLL.ERP_LendSale.GetList(mytrans," Saler='" + dr["Saler"].ToString() + "' and year=" + lyear + " and month=" + lmonth + "").Tables[0];
                if (dtn.Rows.Count == 0)
                {
                    model.lastCount = 0;
                    model.lastMonry = 0;
                }
                else
                {
                    model.lastCount = string.IsNullOrEmpty(dtn.Rows[0]["lastCount"].ToString()) ? 0 : int.Parse(dtn.Rows[0]["lastCount"].ToString());
                    model.lastMonry = string.IsNullOrEmpty(dtn.Rows[0]["lastMonry"].ToString()) ? 0 : decimal.Parse(dtn.Rows[0]["lastMonry"].ToString());
                }
                CRMMis.BLL.ERP_LendSale.Add(mytrans,model);
            }
            mytrans.CommitTran();
            X.Msg.Alert("系统提示", "生成成功！").Show();
        }
        catch (Exception ex)
        {
            mytrans.RollBackTran();
            X.Msg.Alert("系统提示", ex.ToString()).Show();
        }
        
    }

    protected void OnCombYearMonthSelect(object sender, DirectEventArgs e)
    {

    }

    public override DataTable GetPSData(int PageSize, int CurPage, string OrderStr)
    {
        ViewState_OrderSre = OrderStr;
        DataTable dt = null;
            dt = CRMMis.BLL.ERP_LendSale.GetPageList(PageSize, CurPage, _QueryString, OrderStr).Tables[0];
        return dt;
    }
}